package com.oasystem.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.oasystem.domain.Department;
import com.oasystem.domain.PagingBean;

public class DepartmentDAO {
	private JdbcTemplate jdbcTemplate;

	public boolean isDepartmentNameNotExist(String name) 
	{
		return 0==jdbcTemplate.queryForInt("select count(*) from department where departmentName=?",new Object[]{name});
	}
	
	public void insertDepartment(final Department department) {
		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection
						.prepareStatement("insert into department (departmentName,description) values(?,?)");
				ps.setString(1, department.getDepartmentName());
				ps.setString(2, department.getDescription());
				return ps;
			}
		});
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Department> getPagingDepartment(
			PagingBean<Department> pagingBean) {

		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();
		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(id) from department"));

		List<Department> list = jdbcTemplate.query(
				new PreparedStatementCreator() {

					public PreparedStatement createPreparedStatement(
							Connection con) throws SQLException {
						PreparedStatement ps = con
								.prepareStatement("select * from department order by id desc limit ?,?");
						ps.setInt(1, currentRecordIndex);
						ps.setInt(2, pageSize);
						return ps;
					}
				}, new RowMapper<Department>() {

					public Department mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Department department = new Department();
						department.setId(rs.getInt("id"));
						department.setDepartmentName(rs
								.getString("departmentName"));
						department.setDescription(rs.getString("description"));
						return department;
					}
				});
		pagingBean.setData(list);
		return list;
	}

	public void load(Department department) {
		Department result = jdbcTemplate.queryForObject(
				"select * from department where id=?",
				new Object[] { department.getId() },
				new RowMapper<Department>() {

					public Department mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Department department = new Department();
						department.setId(rs.getInt("id"));
						department.setDepartmentName(rs
								.getString("departmentName"));
						department.setDescription(rs.getString("description"));
						return department;
					}
				});
		department.copy(result);
	}

	public void updateDepartment(final Department department) {
		jdbcTemplate
				.update("update department set departmentName=?,description=? where id=?",
						new PreparedStatementSetter() {

							public void setValues(PreparedStatement ps)
									throws SQLException {
								ps.setString(1, department.getDepartmentName());
								ps.setString(2, department.getDescription());
								ps.setInt(3, department.getId());
							}
						});

	}

	public void deleteDepartment(Department department) {
		jdbcTemplate.update("delete from department where id=?",
				new Object[] { department.getId() });
	}

	public List<Department> getAllDepartment() {
		return jdbcTemplate.query("select * from department order by id desc",
				new RowMapper<Department>() {

					public Department mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Department department = new Department();
						department.setId(rs.getInt("id"));
						department.setDepartmentName(rs
								.getString("departmentName"));
						department.setDescription(rs.getString("description"));
						return department;
					}
				});
	}
}
